package com.ambitor.grass.sql.parser;

import java.sql.Connection;
import java.sql.ResultSetMetaData;
import java.sql.Types;

import com.ambitor.grass.sql.dao.MetaDataDao;
import com.ambitor.grass.sql.dao.impl.MetaDataDaoImpl;
import com.ambitor.grass.sql.data.ColumnObject;
import com.ambitor.grass.sql.data.Parameter;
import com.ambitor.grass.util.data.IData;
import com.ambitor.grass.util.data.IDataset;
import com.ambitor.grass.util.date.DateUtils;
import com.ambitor.grass.util.string.StringUtils;

public class SqlParser {

	public static ParsedResult parseInsertSql(Connection conn,
			String tableName, IData data) throws Exception {
		data = StringUtils.DataToUpperCase(data);

		StringBuffer namestr = new StringBuffer();
		StringBuffer valuestr = new StringBuffer();
		Parameter param = new Parameter();

		MetaDataDao metaDataDao = new MetaDataDaoImpl(conn);
		ResultSetMetaData metaData = metaDataDao.getMetaData(tableName);

		for (int i = 1; i <= metaData.getColumnCount(); ++i) {
			String columnName = metaData.getColumnName(i).toUpperCase();
			int columnType = metaData.getColumnType(i);
			Object[] object = getObjectsByColumn(columnName, columnType, data);
			if (!data.containsKey(columnName)) {
				continue;
			}
			namestr.append(object[0] + ",");
			valuestr.append("?,");
			param.add(object[1]);
		}

		String sql = getInsertSql(tableName, StringUtils.trimSuffix(namestr
				.toString(), ","), StringUtils.trimSuffix(valuestr.toString(),
				","));

		return new ParsedResult(sql, param);
	}

	public static BatchParsedResult parseInsertSql(Connection conn,
			String tableName, IDataset dataset) throws Exception {
		dataset = StringUtils.DatasetToUpperCase(dataset);
		StringBuffer namestr = new StringBuffer();
		StringBuffer valuestr = new StringBuffer();

		MetaDataDao metaDataDao = new MetaDataDaoImpl(conn);
		ResultSetMetaData metaData = metaDataDao.getMetaData(tableName);

		Parameter[] params = new Parameter[dataset.count()];
		for (int i = 0; i < params.length; ++i) {
			IData data = dataset.getData(i);
			params[i] = new Parameter();
			for (int j = 1; j <= metaData.getColumnCount(); ++j) {
				String columnName = metaData.getColumnName(j).toUpperCase();
				int columnType = metaData.getColumnType(j);
				Object[] object = getObjectsByColumn(columnName, columnType,
						data);
				if (!data.containsKey(columnName)) {
					continue;
				}
				if (i == 0) {
					namestr.append(object[0] + ",");
					valuestr.append("?,");
				}
				params[i].add(object[1]);
			}
		}

		String sql = getInsertSql(tableName, StringUtils.trimSuffix(namestr
				.toString(), ","), StringUtils.trimSuffix(valuestr.toString(),
				","));

		return new BatchParsedResult(sql, params);
	}

	public static ParsedResult parseQuerySql(Connection conn, String tableName,
			String columns, IData data, String[] keys) throws Exception {
		data = StringUtils.DataToUpperCase(data);
		keys = StringUtils.StringToUpperCase(keys);
		
		ParsedResult parserResult = parseConditionSql(conn, tableName, data,
				keys);
		String sql = getQuerySql(tableName, columns, parserResult
				.getParsedSql());
		return new ParsedResult(sql, parserResult.getParameter());
	}

	public static ParsedResult parseUpdateSql(Connection conn,
			String tableName, IData data, String[] keys) throws Exception {
		data = StringUtils.DataToUpperCase(data);
		keys = StringUtils.StringToUpperCase(keys);
		
		StringBuffer setstr = new StringBuffer();
		Parameter param = new Parameter();

		MetaDataDao metaDataDao = new MetaDataDaoImpl(conn);
		ResultSetMetaData metaData = metaDataDao.getMetaData(tableName);

		for (int i = 1; i <= metaData.getColumnCount(); ++i) {
			String columnName = metaData.getColumnName(i).toUpperCase();
			int columnType = metaData.getColumnType(i);
			Object[] object = getObjectsByColumn(columnName, columnType, data);
			if (!data.containsKey(columnName)) {continue;}
			//data中没有的字段不更新
			setstr.append(object[0] + " = " + "?" + ",");
			param.add(object[1]);
		}

		ParsedResult parseResult = parseConditionSql(conn, tableName, data,
				keys);
		param.addAll(parseResult.getParameter());

		String sql = getUpdateSql(tableName, StringUtils.trimSuffix(setstr
				.toString(), ","), parseResult.getParsedSql());

		return new ParsedResult(sql, param);
	}

	public static BatchParsedResult parseUpdateSql(Connection conn,
			String tableName, IDataset dataset, String[] keys) throws Exception {
		dataset = StringUtils.DatasetToUpperCase(dataset);
		keys = StringUtils.StringToUpperCase(keys);
		
		StringBuffer setstr = new StringBuffer();
		StringBuffer keystr = new StringBuffer();

		MetaDataDao metaDataDao = new MetaDataDaoImpl(conn);
		ResultSetMetaData metaData = metaDataDao.getMetaData(tableName);

		Parameter[] params = new Parameter[dataset.count()];
		for (int i = 0; i < params.length; ++i) {
			IData data = dataset.getData(i);
			params[i] = new Parameter();
			for (int j = 1; j <= metaData.getColumnCount(); ++j) {
				String columnName = metaData.getColumnName(j).toUpperCase();
				int columnType = metaData.getColumnType(j);
				Object[] object = getObjectsByColumn(columnName, columnType,
						data);
				if (!data.containsKey(columnName)) {
					continue;
				}//data中没有的字段不更新
				if (i == 0) {
					setstr.append(object[0] + " = " + "?" + ",");
				}
				params[i].add(object[1]);
			}

			ParsedResult parseResult = parseConditionSql(conn, tableName, data,
					keys);
			if (i == 0) {
				keystr.append(parseResult.getParsedSql());
			}
			params[i].addAll(parseResult.getParameter());
		}

		String sql = getUpdateSql(tableName, StringUtils.trimSuffix(setstr
				.toString(), ","), keystr.toString());

		return new BatchParsedResult(sql, params);
	}

	public static ParsedResult parseDeleteSql(Connection conn,
			String tableName, IData data, String[] keys) throws Exception {
		data = StringUtils.DataToUpperCase(data);
		keys = StringUtils.StringToUpperCase(keys);

		ParsedResult parseResult = parseConditionSql(conn, tableName, data,
				keys);
		String sql = getDeleteSql(tableName, parseResult.getParsedSql());
		return new ParsedResult(sql, parseResult.getParameter());
	}

	public static BatchParsedResult parseDeleteSql(Connection conn,
			String tableName, IDataset dataset, String[] keys) throws Exception {
		dataset = StringUtils.DatasetToUpperCase(dataset);
		keys = StringUtils.StringToUpperCase(keys);
		
		StringBuffer keysql = new StringBuffer();
		Parameter[] params = new Parameter[dataset.count()];
		for (int i = 0; i < params.length; ++i) {
			ParsedResult parseResult = parseConditionSql(conn, tableName,
					dataset.getData(i), keys);

			if (i == 0)
				keysql.append(parseResult.getParsedSql());
			params[i] = parseResult.getParameter();
		}

		String sql = getDeleteSql(tableName, keysql.toString());
		return new BatchParsedResult(sql, params);
	}

	public static ParsedResult parseConditionSql(Connection conn,
			String tableName, IData data, String[] keys) throws Exception {
		data = StringUtils.DataToUpperCase(data);
		keys = StringUtils.StringToUpperCase(keys);

		MetaDataDao metaDataDao = new MetaDataDaoImpl(conn);
		StringBuffer sqlstr = new StringBuffer();
		Parameter param = new Parameter();

		if (keys == null) {
			keys = metaDataDao.getPrimaryKeys(tableName);
		}
		ResultSetMetaData metaData = metaDataDao.getMetaData(tableName);
		for (int i = 0; i < keys.length; ++i) {
			String key = keys[i];
			Object value = data.get(key);

			int columnType = metaData.getColumnType(i + 1);
			sqlstr.append(" and "
					+ (isDatetimeColumn(columnType) ? getTocharSql(key, value
							.toString()) : key) + " = " + "?");
			param.add(value);
		}
		return new ParsedResult(StringUtils.trimPrefix(sqlstr.toString(),
				" and"), param);
	}

	public static Object[] getObjectsByColumn(String columnName,
			int columnType, IData data) throws Exception {
		data = StringUtils.DataToUpperCase(data);

		Object columnValue = data.get(columnName);

		if (isDatetimeColumn(columnType)) {
			if (columnValue != null) {
				if ("".equals(columnValue)) {
					columnValue = null;
				} else {
					columnValue = DateUtils.encodeTimestamp(columnValue
							.toString());
				}
			}
		} else if (columnType == Types.LONGVARCHAR) {
			if (columnValue != null && !"".equals(columnValue)) {
				columnValue = new ColumnObject(columnName, columnValue,
						Types.LONGVARCHAR);
			}
		}

		return new Object[] { columnName, columnValue };
	}

	private static boolean isDatetimeColumn(int columnType) throws Exception {
		return columnType == Types.DATE || columnType == Types.TIME
				|| columnType == Types.TIMESTAMP;
	}

	private static String getTocharSql(String value, String format)
			throws Exception {
		String dformat = getDateFormat(format);
		return "DATE_FORMAT(" + value + ", '" + dformat + "')";
	}

	private static String getDateFormat(String value) throws Exception {
		switch (value.length()) {
		case 4:
			return "%Y";
		case 6:
			return "%Y%m";
		case 7:
			return "%Y-%m";
		case 8:
			return "%Y%m%d";
		case 10:
			return "%Y-%m-%d";
		case 13:
			return "%Y-%m-%d %H";
		case 16:
			return "%Y-%m-%d %H:%i";
		case 19:
			return "%Y-%m-%d %H:%i:%s";
		}
		return null;
	}

	private static String getQuerySql(String tableName, String columns,
			String keystr) {
		StringBuffer str = new StringBuffer();
		if ((columns == null) || columns.equals(""))
			columns = "*";
		str.append("select " + columns + " from " + tableName);
		str.append(" where ");
		str.append(keystr);
		return str.toString();
	}

	private static String getInsertSql(String tableName, String namestr,
			String valuestr) {
		StringBuffer str = new StringBuffer();
		str.append("insert into " + tableName);
		str.append("(" + namestr + ")");
		str.append(" values ");
		str.append("(" + valuestr + ")");
		return str.toString();
	}

	private static String getUpdateSql(String tableName, String setstr,
			String keystr) {
		StringBuffer str = new StringBuffer();
		str.append("update " + tableName + " set ");
		str.append(setstr);
		str.append(" where ");
		str.append(keystr);
		return str.toString();
	}

	private static String getDeleteSql(String tableName, String keystr) {
		StringBuffer str = new StringBuffer();
		str.append("delete from " + tableName);
		str.append(" where ");
		str.append(keystr);
		return str.toString();
	}
}
